Public Class clsCategoriesDataAccess
    Private m_DBConn As System.Data.SqlClient.SqlConnection
    Private m_Errors As List(Of System.Exception)
    Public Sub New(ByVal dbConn As String)
        MyBase.New()
        Me.m_DBConn = New System.Data.SqlClient.SqlConnection(dbConn)
        Me.m_Errors = New List(Of System.Exception)
    End Sub
    Public ReadOnly Property Errors() As List(Of System.Exception)
        Get
            Return Me.m_Errors
        End Get
    End Property
    Public Function SelectList(ByVal sql As String, ByVal commandType As System.Data.CommandType, ByVal parameters() As System.Data.SqlClient.SqlParameter) As List(Of clsCategories)
        Dim objs As List(Of clsCategories) = New List(Of clsCategories)
        Dim comm As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sql, Me.m_DBConn)
        comm.CommandType = commandType
        If ((Not (parameters) Is Nothing) _
           AndAlso (parameters.Length > 0)) Then
            comm.Parameters.AddRange(parameters)
        End If
        Try
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Open) Then
                Me.m_DBConn.Open()
            End If
            Dim dr As System.Data.SqlClient.SqlDataReader = comm.ExecuteReader

            Do While dr.Read
                Dim obj As clsCategories = New clsCategories
                obj.Fill(dr)
                objs.Add(obj)

            Loop
            dr.Close()
        Catch ex As System.Exception
            Dim newEx As System.Exception = New System.Exception("From SelectList. See inner exception.", ex)
            Me.Errors.Add(newEx)
        Finally
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Closed) Then
                Me.m_DBConn.Close()
            End If
            comm.Dispose()
        End Try
        Return objs
    End Function
    Public Function SelectSingle(ByVal sql As String, ByVal commandType As System.Data.CommandType, ByVal parameters() As System.Data.SqlClient.SqlParameter) As clsCategories
        Dim obj As clsCategories = New clsCategories
        Dim comm As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sql, Me.m_DBConn)
        comm.CommandType = commandType
        If ((Not (parameters) Is Nothing) _
           AndAlso (parameters.Length > 0)) Then
            comm.Parameters.AddRange(parameters)
        End If
        Try
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Open) Then
                Me.m_DBConn.Open()
            End If
            Dim dr As System.Data.SqlClient.SqlDataReader = comm.ExecuteReader
            If dr.Read Then
                obj.Fill(dr)
            End If
            dr.Close()
        Catch ex As System.Exception
            Dim newEx As System.Exception = New System.Exception("From SelectSingle. See inner exception.", ex)
            Me.Errors.Add(newEx)
        Finally
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Closed) Then
                Me.m_DBConn.Close()
            End If
            comm.Dispose()
        End Try
        Return obj
    End Function
    Public Function Delete(ByVal obj As clsCategories) As Integer
        Dim rows As Integer
        Dim sql As String = "[dbo].[TBL_DOC_CAT_Delete]"
        Dim comm As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sql, Me.m_DBConn)
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.Parameters.AddWithValue("@DOC_CAT_ID", obj.DOC_CAT_ID)
        comm.Parameters.AddWithValue("@DOC_CAT_TYPE", obj.DOC_CAT_TYPE)
        comm.Parameters.AddWithValue("@DOC_CAT_DESC", obj.DOC_CAT_DESC)
        comm.Parameters.AddWithValue("@DOC_CAT_ACTIVE", obj.DOC_CAT_ACTIVE)
        Try
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Open) Then
                Me.m_DBConn.Open()
            End If
            rows = comm.ExecuteNonQuery
        Catch ex As System.Exception
            rows = 0
            Dim newEx As System.Exception = New System.Exception("From Delete. See inner exception.", ex)
            Me.Errors.Add(newEx)
        Finally
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Closed) Then
                Me.m_DBConn.Close()
            End If
            comm.Dispose()
        End Try
        Return rows
    End Function
    Public Function [Select](ByVal DOC_CAT_ID As Integer, ByVal DOC_CAT_TYPE As String, ByVal DOC_CAT_DESC As String, ByVal DOC_CAT_ACTIVE As Boolean) As clsCategories
        Dim sql As String = "[dbo].[TBL_DOC_CAT_Select]"
        Dim type As System.Data.CommandType = System.Data.CommandType.StoredProcedure
        Dim parameters As List(Of System.Data.SqlClient.SqlParameter) = New List(Of System.Data.SqlClient.SqlParameter)
        parameters.Add(New System.Data.SqlClient.SqlParameter("@DOC_CAT_ID", DOC_CAT_ID))
        parameters.Add(New System.Data.SqlClient.SqlParameter("@DOC_CAT_TYPE", DOC_CAT_TYPE))
        parameters.Add(New System.Data.SqlClient.SqlParameter("@DOC_CAT_DESC", DOC_CAT_DESC))
        parameters.Add(New System.Data.SqlClient.SqlParameter("@DOC_CAT_ACTIVE", DOC_CAT_ACTIVE))
        Return Me.SelectSingle(sql, type, parameters.ToArray)
    End Function
    Public Function Save(ByVal obj As clsCategories) As clsCategories
        Dim result As clsCategories = Nothing
        Dim sql As String = "[dbo].[TBL_DOC_CAT_Save]"
        Dim comm As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sql, Me.m_DBConn)
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.Parameters.AddWithValue("@DOC_CAT_ID", obj.DOC_CAT_ID)
        comm.Parameters.AddWithValue("@DOC_CAT_TYPE", obj.DOC_CAT_TYPE)
        comm.Parameters.AddWithValue("@DOC_CAT_DESC", obj.DOC_CAT_DESC)
        comm.Parameters.AddWithValue("@DOC_CAT_ACTIVE", obj.DOC_CAT_ACTIVE)
        Try
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Open) Then
                Me.m_DBConn.Open()
            End If
            Dim dr As System.Data.SqlClient.SqlDataReader = comm.ExecuteReader
            If dr.Read Then
                result = New clsCategories
                result.Fill(dr)
            End If
            dr.Close()
        Catch ex As System.Exception
            Dim newEx As System.Exception = New System.Exception("From Save. See inner exception.", ex)
            Me.Errors.Add(newEx)
        Finally
            If (Me.m_DBConn.State <> System.Data.ConnectionState.Closed) Then
                Me.m_DBConn.Close()
            End If
            comm.Dispose()
        End Try
        Return result
    End Function
    Public Function SelectAll() As List(Of clsCategories)
        Dim sql As String = "[dbo].[TBL_DOC_CAT_SelectAll]"
        Dim type As System.Data.CommandType = System.Data.CommandType.StoredProcedure
        Return Me.SelectList(sql, type, Nothing)
    End Function
End Class

